package com.apes.scm.masterdata.frame.repository;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.frame.model.Branch;
import com.apes.scm.masterdata.frame.model.Dept;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author HuJianlin
 * @create 2018-03-17 11:04
 */
public interface DeptRepository extends  CustomRepository<Dept, String> {

    @Query("select T from Dept T where 1 = 1 and (id like %:value%  or name like %:value%) ")
    Page<Dept> find( @Param("value") String value, Pageable pageable);

    @Query("select T from Dept T where 1 = 1 and (id like %:value%  or name like %:value%)")
    List<Dept> findLikeName( @Param("value") String value);

    @Query(value = "SELECT a.* FROM mas_dept a, mas_dept_type b WHERE a.id = b.DEPT_ID and (id like %:value%  or name like %:value%) and b.id = '000007'", nativeQuery = true)
    List<Dept> findLikeNameAndDeptType(@Param("value") String value);

    @Query("SELECT T from Dept T where 1 = 1 and branch = :branch")
    List<Dept> findByBranch(@Param("branch") Branch branch);

    @Query(value = "SELECT T.* from mas_dept T where branch_id = :branch",nativeQuery = true)
    List<Dept> findByBranchId(@Param("branch") String branch);

    @Query(value = "SELECT T.* from mas_dept T where profit_center_id = :profitCenterId",nativeQuery = true)
    List<Dept> findByProfitCenterId(@Param("profitCenterId") String profitCenterId);


    @Query("select s " +
            "from Dept s where to_char(s.lastUpdatedStamp,'yyyy-mm-dd hh24:mi:ss.ff3' ) > :times or (s.id > :id and to_char(s.lastUpdatedStamp,'yyyy-mm-dd hh24:mi:ss.ff3' ) = :times)  " +
            " order by s.lastUpdatedStamp, s.id asc ")
    List<Dept> findByLastUpdatedStampAndId(@Param("times") String times, @Param("id") String id, Pageable pageable);

    @Query(value = "select operation from mas_corporate c WHERE c.party_id = (select p.party_id from mas_party_role p WHERE p.id = (select T.COMPANY_ID from mas_dept t WHERE t.id = :deptId))",nativeQuery = true)
    String findOperationByDept(@Param("deptId") String deptId);

    @Query(value = "SELECT t.branch_id FROM mas_dept T where t.id in :ids",nativeQuery = true)
    List<String> findDeptByIds(@Param("ids")List<String> ids);

    @Query("select T from Dept T where T.id = :id")
    Dept findDept(@Param("id") String id);


    Dept findByOrigin(@Param("origin") String origin);

    Dept findByAndBranchId(@Param("branchId") String branchId);

    List<Dept> findByName(String name);


    @Query(value = "SELECT a.* FROM mas_dept A, mas_dept_type b WHERE A.id = b.DEPT_ID and b.ID = :deptType AND A.company_id = :company  AND ROWNUM <= 1 ", nativeQuery = true)
    Dept findByDeptTypeIdAndCompanyId(String deptType, String company);

    @Query("select T from Dept T where T.name = :name")
    Dept findByIdAndName(String name);

    @Query(value = "SELECT T.* FROM MAS_DEPT T, mas_dept_type b WHERE t.id = b.DEPT_ID and b.ID = :type AND T.COMPANY_ID  = :id and ROWNUM = 1 order by T.create_date  ",nativeQuery = true)
    Dept findByPartyRoleAndDeptType(@Param("id") String id,@Param("type") String type);

    @Query("SELECT A.id as id ,A.name as name FROM Dept A WHERE A.id = :id")
    Map<String, Object> findIdAndName(@Param("id") String deptId);

    @Query(value = "SELECT T.* FROM MAS_DEPT T, mas_dept_type b WHERE t.id = b.DEPT_ID and b.ID = '000007' AND NOT EXISTS (SELECT 1 FROM MAS_STORE S WHERE S.MERCHANT_ID IS NOT NULL and S.DEPT_ID = T.ID)",nativeQuery = true)
    List<Dept> findAllByStoreNoneMerchant();

    @Query(value = "SELECT T.* FROM MAS_DEPT T, mas_dept_type b WHERE t.id = b.DEPT_ID and b.ID = '000007' AND EXISTS (SELECT 1 FROM MAS_STORE S WHERE S.MERCHANT_ID = :merchantId and S.DEPT_ID = T.ID)",nativeQuery = true)
    List<Dept> findAllByMerchant(@Param("merchantId") String merchantId);

    @Query(value = "SELECT T.* FROM MAS_DEPT T , mas_dept_type b WHERE t.id = b.DEPT_ID and b.ID = '000007'  and t.valid =1  and t.company_id=:company",nativeQuery = true)
    List<Dept> findAllByCompany(@Param("company") String company);

}
